package com.other.generater;

import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * 这里链接默认是mysql
 */
public class JDBCUtils {
    private static Connection connection;
    private static java.util.Date start;
    private static java.util.Date end;
    private static String dbtype="mysql";//数据库类型
    private static String _dbname="rqt_v2";//数据库名
    static String url = "jdbc:mysql://ruiqiantong.mysql.rds.aliyuncs.com:3306/rqt_v2?"
            + "user=rqtread&password=wjmdsrqbao@2017&useUnicode=true&characterEncoding=UTF8";
    static String url2 = "jdbc:mysql://rqtbackup.mysql.rds.aliyuncs.com:3306/rqt_v3?"
            + "user=rqtroot&password=7z1175uDe&useUnicode=true&characterEncoding=UTF8";

    public static String setUrl(String db,String host,String port,String dbname,String user,String password){
        String url2="";
        _dbname=dbname;
        if (db.equals("mysql")) {
            url2="jjdbc:mysql://"+host+":"+port+"/"+dbname+"?user="+user+"&password="+password;
        }else if (db.equals("oracle")) {
            //jdbc:oracle:thin:username/password@//x.x.x.1:1522/ABCD
            url2="jdbc:oracle:thin:"+user+"/"+password+"@//"+host+":"+port+"/"+dbname;
        }else if (db.equals("db2")) {
            //jdbc:db2://ServerIP:Port/databasename
            url2="jdbc:db2://";
        }else {
            System.err.print("不支持的数据库url");
        }
        url=url2;
        return url2;
    }
    /**
     * 传递数据库驱动和数据库链接
     * @param className
     * @param urlnew
     * @return
     */
    public static Connection getConnection(String dbtypenew,String urlnew){
        try {
            start=new Date();
            String className="";
            if (Objects.isNull(dbtypenew)) {
                className= DBDriver.mysql;
            }else if(dbtypenew.equals("mysql")){
                className= DBDriver.mysql;
            }else if(dbtypenew.equals("oracle")){
                className= DBDriver.oracle;
            }else if(dbtypenew.equals("db2")){
                className= DBDriver.db2;
            }else{
                System.err.print("不支持的数据库");
            }
            if (Objects.nonNull(urlnew)) {
                url=urlnew;
            }
            Class.forName(className);
            connection = DriverManager.getConnection(url);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return   connection;
    }

    /**
     * sql查询
     * @param sql
     * @return
     */
    public static ResultSet getSql(String sql){
        try {
            Statement stmt = connection.createStatement();
            return stmt.executeQuery(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * sql更新操作
     * @param sql
     * @return
     */
    public static int updateSql(String sql){
        try {
            Statement stmt = connection.createStatement();
            return stmt.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 遍历结果集转换结果
     * @param cps
     * @throws SQLException
     */
    public static List<Map<String,Object>> getResult(ResultSet cps) {
        List<Map<String,Object>> result=new LinkedList<>();
        try {
            int size=cps.getMetaData().getColumnCount();//列数
//            System.out.println("\n\n#######################《"+cps.getMetaData().getTableName(1)+"》################################");
            while (cps.next()) {
                Map<String,Object> map=new LinkedHashMap<>();
                for (int i = 1; i < size; i++) {
                    String name = cps.getMetaData().getColumnLabel(i);
//                    System.out.print(name);
                    Object val = cps.getObject(name);// 内容可能为空
//                    System.out.print(":" + val+",");
                    map.put(name,val);
                    //System.out.println("行:"+cps.getObject(i).toString());
                }
                result.add(map);
            }
        }catch (Exception e){
            return result;
        }
        return result;
    }
    /**
     * 遍历结果集转换结果  并且只打印相关结果
     * @param cps
     * @throws SQLException
     */
    public static List<Map<String,Object>> getResultForName(ResultSet cps,Object ...Objects) {
        List<Map<String,Object>> result=new LinkedList<>();
        try {
            int size=cps.getMetaData().getColumnCount();//列数
            System.out.println("\n\n#######################《"+cps.getMetaData().getTableName(1)+"》################################");
            while (cps.next()) {
                Map<String,Object> map=new LinkedHashMap<>();
                for (int i = 1; i < size; i++) {
                    String name = cps.getMetaData().getColumnLabel(i);
                    Object val = cps.getObject(name);// 内容可能为空
                    for (Object o:Objects) {
                        if (o.equals(name)) {
                            System.out.print(name);
                            System.out.print(":" + val+",");
                        }
                    }
                    map.put(name,val);
                    //System.out.println("行:"+cps.getObject(i).toString());
                }
                result.add(map);
                System.out.println("\n---------------------------------");
            }
        }catch (Exception e){
            return result;
        }
        return result;
    }

    /**
     * 查询表名+表信息
     */
    public static List<TableInfo> getTable(){
        String sql="";
        List<TableInfo> tableInfos=new LinkedList<>();
        if (dbtype.equals("mysql")) {
            sql="select TABLE_NAME,TABLE_COMMENT,CREATE_TIME from information_schema.tables where table_schema='"+_dbname+"' and table_type='base table'";
            ResultSet rs=getSql(sql);
            try {
                while (rs.next()){
                    TableInfo tableInfo=new TableInfo();
                    String TABLE_NAME=rs.getObject("TABLE_NAME").toString();
                    String TABLE_COMMENT=rs.getObject("TABLE_COMMENT").toString();
                    String CREATE_TIME=rs.getObject("CREATE_TIME").toString();
                    tableInfo.setComment(TABLE_COMMENT);
                    tableInfo.setName(TABLE_NAME);
                    tableInfo.setCreatetime(CREATE_TIME);
                    tableInfo.setTableColInfos(getTableColumnsInfo(TABLE_NAME));
                    tableInfos.add(tableInfo);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else if (dbtype.equals("oracle")) {

        }else if (dbtype.equals("db2")) {

        }

        return tableInfos;

    }

    public  static TableInfo getTableByName(String tableName){
        String sql="";
        TableInfo tableInfo=new TableInfo();
        if (dbtype.equals("mysql")) {
            sql="select TABLE_NAME,TABLE_COMMENT,CREATE_TIME from information_schema.tables where table_schema='"+_dbname+"' and table_type='base table' and TABLE_NAME='"+tableName+"'";
            ResultSet rs=getSql(sql);
            try {
                if (rs.next()){
                    String TABLE_NAME=rs.getObject("TABLE_NAME").toString();
                    String TABLE_COMMENT=rs.getObject("TABLE_COMMENT").toString();
                    String CREATE_TIME=rs.getObject("CREATE_TIME").toString();
                    tableInfo.setComment(TABLE_COMMENT);
                    tableInfo.setName(TABLE_NAME);
                    tableInfo.setCreatetime(CREATE_TIME);
                    tableInfo.setTableColInfos(getTableColumnsInfo(TABLE_NAME));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else if (dbtype.equals("oracle")) {

        }else if (dbtype.equals("db2")) {

        }
        return tableInfo;
    }
    /**
     * 根据表名查询列的相关信息
     */
    public static List<TableColInfo> getTableColumnsInfo(String tableName){
        List<TableColInfo> tableColInfos =new LinkedList<>();
        String infosql="";
        if (dbtype.equals("mysql")) {
            infosql="SHOW FULL COLUMNS FROM "+tableName;
        }else if (dbtype.equals("oracle")) {
            infosql="SELECT * FROM user_tab_columns WHERE TABLE_NAME="+tableName;
        }else if (dbtype.equals("db2")) {
            infosql="SHOW FULL COLUMNS FROM '"+tableName+"";
        }else {
            System.err.print("不支持的数据库");
            return null;
        }
        ResultSet resultSet=getSql(infosql);
        String colname="";
        String comment="";
        String dbtype2="";
        try {
            while (resultSet.next()) {
                TableColInfo tableColInfo =new TableColInfo();
                if (dbtype.equals("mysql")) {
                    colname=resultSet.getObject("Field").toString();
                    comment=resultSet.getObject("Comment").toString();
                    dbtype2=resultSet.getObject("Type").toString();
                    tableColInfo.setColname(colname);
                    tableColInfo.setComment(comment);
                    tableColInfo.setDbtype(dbtype2);
                }else if (dbtype.equals("oracle")) {

                }else if (dbtype.equals("db2")) {

                }
                tableColInfos.add(tableColInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return tableColInfos;
    }

    /**
     * 关闭链接
     */
    public static void close(){
        try {
            end=new Date();
            System.out.println("\n消耗时间:"+(end.getTime()-start.getTime())+"ms");
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public  void  test(){
        if (dbtype.equals("mysql")) {

        }else if (dbtype.equals("oracle")) {

        }else if (dbtype.equals("db2")) {

        }
    }

    public  interface  DBDriver{
        public static  final String mysql="com.mysql.jdbc.Driver";
        public static  final String oracle="oracle.jdbc.driver.OracleDriver";
        public static  final String db2="com.ibm.db2.jcc.DB2Driver";
    }
}
